package com.sl.au.repository;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import com.sl.au.entity.EventType;

@Transactional(readOnly = true)
public interface EventTypeRepository extends JpaRepository<EventType, String> {

	@Query(" select a from EventType a where a.eventParent is not null order by a.eventLevel.extended1 asc,a.eventOrder asc")
	List<EventType> findAllNodes();

	@Query(" select a from EventType a where a.eventParent is null and ( a.eventName like %?1% or a.eventIdentify like %?1% or a.eventLevel.title like %?1%) ")
	Page<EventType> getEventTypeByKey(String key, Pageable pageable);

	@Query(" select count(*) from EventType a where a.eventParent is null and ( a.eventName like %?1% or a.eventIdentify like %?1% or a.eventLevel.title like %?1%) ")
	long getEventTypeByKeyCount(String key);

	@Query(" select a from EventType a where a.eventParent is null ")
	Page<EventType> getEventType(Pageable pageable);

	@Query(" select count(*) from EventType a where a.eventParent is null ")
	long getEventTypeCount();

	@Modifying
	@Query("update EventType a set a.eventParent = null where a.id=?1")
	int setEventType(String id);

	@Query(" select IFNULL(max(a.eventOrder),-2) + 2 from EventType a where a.eventParent = ?1 ")
	int getEventOrder(String parentId);

	@Query(" select a from EventType a where a.eventParent = ?1 order by a.eventOrder asc")
	List<EventType> getEventTypesByParent(String parentId);
	
	@Query(" select a from EventType a where a.isMobileShow = '0' and a.eventParent = ?1 order by a.eventOrder asc")
	List<EventType> getMobileEventTypesByParent(String parentId);

	@Query("select c from EventType c where c.id in (select a.eventParent from EventType a where a.eventParent in (select b.id from EventType b where b.eventParent = ?1)) order by c.eventOrder asc")
	List<EventType> getEventTypesHasChildByParent(String parentId);

	@Query("select c from EventType c where c.eventParent = ?1 and c.id not in (select a.eventParent from EventType a where a.eventParent in (select b.id from EventType b where b.eventParent = ?1)) order by c.eventOrder asc")
	List<EventType> getEventTypesHasNoChildByParent(String parentId);

	EventType findTopByEventParentAndEventIdentify(String eventParent, String eventIdentify);

	Page<EventType> findByEventParent(String eventParent, Pageable pageable);

	@Query(" select a from EventType a where a.eventParent = ?1 and ( a.eventName like %?2% or a.eventIdentify like %?2% or a.eventLevel.title like %?2%) ")
	Page<EventType> findByeventParentAndKey(String id, String key, Pageable pageable);

	@Query("select a from EventType a where a.id not in (select ue.eventType.id from UserEventTypeBindInfo ue) and ( a.eventName like %?1% or a.eventIdentify like %?1%)")
	Page<EventType> findOuterTypeOnlyCon(Pageable pageable, String con);

	@Query("select a from EventType a where a.id not in (select ue.eventType.id from UserEventTypeBindInfo ue)")
	Page<EventType> findOuterTypeNone(Pageable pageable);

	@Query("select a from EventType a where a.id not in (select ue.eventType.id from UserEventTypeBindInfo ue where ue.user.hierarchy.id=?1) and ( a.eventName like %?2% or a.eventIdentify like %?2%)")
	Page<EventType> findOuterTypeByKeyAndCon(Pageable pageable, String key, String con);

	@Query("select a from EventType a where a.id not in (select ue.eventType.id from UserEventTypeBindInfo ue where ue.user.hierarchy.id=?1)")
	Page<EventType> findOuterTypeOnlyKey(Pageable pageable, String key);

	@Query("select a from EventType a where a.id in (select ue.eventType.id from UserEventTypeBindInfo ue) and ( a.eventName like %?1% or a.eventIdentify like %?1%)")
	Page<EventType> findInnerTypeOnlyCon(Pageable pageable, String con);

	@Query("select a from EventType a where a.id in (select ue.eventType.id from UserEventTypeBindInfo ue)")
	Page<EventType> findInnerTypeNone(Pageable pageable);

	@Query("select a from EventType a where a.id in (select ue.eventType.id from UserEventTypeBindInfo ue where ue.user.hierarchy.id=?1) and ( a.eventName like %?2% or a.eventIdentify like %?2%)")
	Page<EventType> findInnerTypeCountByKeyAndCon(Pageable pageable, String key, String con);

	@Query("select a from EventType a where a.id in (select ue.eventType.id from UserEventTypeBindInfo ue where ue.user.hierarchy.id=?1)")
	Page<EventType> findInnerTypeOnlyKey(Pageable pageable, String key);

	@Query(" select a from EventType a where a.eventParent = ?1")
	List<EventType> findByParentId(String parentId);
	
	@Query(" select a from EventType a where a.eventName = ?1")
	List<EventType> findByName(String name);
}
